
package com.dstz.form.util;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dstz.base.api.exception.BusinessMessage;
import com.dstz.base.core.util.BeanUtils;
import com.dstz.base.core.util.PropertyUtil;
import com.dstz.base.core.util.StringUtil;
import com.dstz.form.constants.BooleanConstants;
import com.dstz.form.constants.SqlConstants;
import com.dstz.form.constants.ViewConstants.FieldsOperTypeCode;
import com.dstz.form.constants.ViewConstants.GroupByType;
import com.dstz.form.constants.ViewConstants.ParamNameConstants;
import com.dstz.form.constants.ViewConstants.SearchParamsType;
import com.dstz.form.constants.ViewConstants.ViewDataTypeConstants;
import com.dstz.form.constants.ViewConstants.ViewSetDataConstants;
import com.dstz.form.model.custsql.FormCustSql;
import com.dstz.form.model.custsql.FormCustSqlFields;
import com.dstz.form.vo.SqGroupByVO;
import com.dstz.form.vo.SqlConditionVO;
import com.dstz.form.vo.SqlConstructVO;
import com.dstz.form.vo.SqlQueryDataVO;
import com.dstz.form.vo.SqlSearchVO;
import com.dstz.form.vo.SqlViewVO;
import com.dstz.org.api.model.IGroup;
import com.dstz.sys.util.ContextUtil;
import com.fasterxml.jackson.databind.ObjectMapper;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.OrderByElement;

import javax.servlet.http.HttpServletRequest;

/**
 * Sql数据工具
 * @author xianggang
 * @date 2019年9月2日 下午2:04:47
 */
public class ViewUtil {
	
	protected static Logger logger = LoggerFactory.getLogger(ViewUtil.class);
	
	// 获取字段统计数据的脚本
	public static String getQuerySumSql(String sumSqlColumn, String realSqlColumn, String sql) {
		
		if (StringUtils.isEmpty(sumSqlColumn)) {
			return "";
		}
		String[] realSqlColumnArr = realSqlColumn.split(", "); // 注意这个空格不能丢掉
		sql = SqlUtil.optimizeSql(sql, realSqlColumnArr);
		String sumSql = "select " + sumSqlColumn + " from (" + sql + ") B ";
		return sumSql;
	}
	
	// 获取查询数据的分页脚本
	public static String getQueryDataSql(String realSqlColumn, String sql, int curPage, int pageSize) {
		
		if (curPage <= 0) {
			curPage = 1;
		}
		// 分页
		int offset = (curPage - 1) * pageSize;
		
		String limit = " limit " + offset + "," + pageSize;
		
		String dataSql = sql + limit;
		String[] realSqlColumnArr = realSqlColumn.split(", "); // 注意这个空格不能丢掉
		dataSql = SqlUtil.optimizeSql(dataSql, realSqlColumnArr);
		
		return dataSql;
	}
	
	/**
	* 获取自定义sql脚本的结构
	* 
	* @param view
	* @param queryData
	* @return
	* @return TmSqlAndDsAliasVO
	* @author xianggang
	* @date 2018年4月5日 下午4:17:20
	*/
	public static SqlConstructVO getSqlConstruct(FormCustSql view, SqlQueryDataVO sqlQueryDataVO) {
		
		String sql = view.getSqlContent();
		sql = sql.replace("\t", "");
		
		JSONObject sqlFieldTableAndNameMap = new JSONObject();// 所有字段
		
		List<FormCustSqlFields> fieldsList = view.getFieldsList();
		for (FormCustSqlFields fieldVO : fieldsList) {
			String name = fieldVO.getName();
			String tableAlias = fieldVO.getTableAlias();
			if (StringUtil.isNotEmpty(tableAlias)) {
				name = tableAlias + "." + name;
			}
			sqlFieldTableAndNameMap.put(fieldVO.getFieldName(), name);
			
		}
		
		// 设置查询条件
		List<SqlConditionVO> conditionsList = JSONObjectUtil.toList(view.getConditions(), SqlConditionVO.class);
		SqlConstructVO SqlConstructVO = ViewUtil.getQuerySql(sql, sqlQueryDataVO, conditionsList, view.getExpand(),
		        sqlFieldTableAndNameMap);
		
		SqlConstructVO.setDsAlias(view.getDsKey());
		
		return SqlConstructVO;
	}
	
	/**
	 * queryData
	 * 
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param queryData
	 * @param sqlFieldTableAndNameMap
	 * @param contidtions
	 * 
	 * @return String
	 * 
	 * @author xianggang
	 * @date: 2016年2月2日 下午9:26:05
	 */
	public static SqlConstructVO getQuerySql(String sql, SqlQueryDataVO sqlQueryDataVO, List<SqlConditionVO> conditions,
	        String expand, JSONObject sqlFieldTableAndNameMap) {
		
		String orderBy = "";
		String where = "";
		String viewWhere = "";
		String groupBy = "";
		String pureSql = sql;
		if (sqlQueryDataVO != null) {
			// //加上默认查询条件
			sqlQueryDataVO = getQueryDataVo(sqlQueryDataVO, conditions, expand);
			
			// 获取排序条件 支持多排序
			orderBy = sqlQueryDataVO.getOrderBy();
			if (StringUtil.isNotEmpty(orderBy)) {
				String[] orderByArr = orderBy.split(",");
				orderBy = " order by ";
				for (String order : orderByArr) {
					String[] temp = order.split("#");
					if (BeanUtils.isNotEmpty(temp) && temp.length == 2) {
						orderBy += temp[0] + " " + temp[1] + ",";
					}
				}
				orderBy = orderBy.substring(0, orderBy.length() - 1);
				
			}
			
		} else {
			// //加上默认查询条件
			sqlQueryDataVO = getQueryDataVo(null, conditions, expand);
		}
		
		// 获取查询条件
		SqlConstructVO sqlConstructVO = getWhere(sqlQueryDataVO, sqlFieldTableAndNameMap);
		where = sqlConstructVO.getWhere();
		viewWhere = sqlConstructVO.getViewWhere();
		
		// 判断sql中是否有 group by
		groupBy = "";
		if (sqlQueryDataVO.getGroupBy() != null) {
			List<SqGroupByVO> groupByList = sqlQueryDataVO.getGroupBy();
			String tempGroupBy = "";
			for (SqGroupByVO groupByData : groupByList) {
				int groupType = groupByData.getGroupType();
				String name = groupByData.getName();
				String val = groupByData.getVal();
				if (StringUtil.isEmpty(val) || Util.equals(val, "null")) {
					continue;
				}
				name = sqlFieldTableAndNameMap.getString(name);
				switch (groupType) {
					case GroupByType.NORMAL:
						tempGroupBy += name + ",";
					break;
					case GroupByType.TIME:
						String groupByTime = "date_format(" + name + ",'" + val + "')";
						sql = sql.replaceAll(name, groupByTime);
						tempGroupBy += groupByTime + ",";
					break;
					case GroupByType.CUSTOMER:
						tempGroupBy += val + ",";
					break;
				
					default:
					break;
				}
			}
			if (!StringUtil.isEmpty(tempGroupBy)) {
				tempGroupBy = tempGroupBy.substring(0, tempGroupBy.length() - 1);
				groupBy = " group by " + tempGroupBy;
			}
			
		}
		
		// 判断sql中是否有where
		boolean hasWhereExp = SqlUtil.hasWhereExp(sql);
		
		if (!hasWhereExp && !StringUtil.isEmpty(where)) {
			where = " where 1=1 " + where;
			viewWhere = " where 1=1 " + viewWhere;
		}
		
		orderBy = StringUtil.ifNullReturnEmpty(orderBy);
		groupBy = StringUtil.ifNullReturnEmpty(groupBy);
		
		sqlConstructVO.setPureSql(pureSql);
		sqlConstructVO.setGroupBy(groupBy);
		sqlConstructVO.setOrderBy(orderBy);
		sqlConstructVO.setWhere(where);
		sqlConstructVO.setViewWhere(viewWhere);
		sqlConstructVO.setFreemarkerParams(sqlQueryDataVO.getFreemarkerParams());
		sqlConstructVO.setQueryData(sqlQueryDataVO.getQueryData());
		
		// 拼装sql
		if (StringUtil.isEmpty(where)) {
			sql = sql.replaceAll(";", "") + groupBy + orderBy;
			sqlConstructVO.setSql(sql);
			return sqlConstructVO;
		}
		
		// 获取group by
		List<Expression> groupByExp = SqlUtil.getGroupByExps(sql);
		
		// 清除group by
		if (!CollectionUtils.isEmpty(groupByExp)) {
			sql = SqlUtil.removeGroupByExp(sql);
		}
		
		// 获取order by
		List<OrderByElement> orderByExp = SqlUtil.getOrderByExp(sql);
		
		// 移除orderby
		if (!CollectionUtils.isEmpty(orderByExp)) {
			sql = SqlUtil.removeOrderByExp(sql);
		}
		
		// 加上新的group by 和 order by 
		sql = sql + where + groupBy + orderBy;
		
		// 解析新的group by 和 order by 
		if (StringUtil.isNotEmpty(groupBy)) {
			groupByExp.addAll(SqlUtil.getGroupByExps(sql));
			sql = SqlUtil.removeGroupByExp(sql);
		}
		if (StringUtil.isNotEmpty(orderBy)) {
			orderByExp.addAll(SqlUtil.getOrderByExp(sql));
			sql = SqlUtil.removeOrderByExp(sql);
		}
		
		// 加回group by
		sql = SqlUtil.addGroupByExp(sql, groupByExp);
		
		// 加回order by
		sql = SqlUtil.addOrderByExp(sql, orderByExp);
		
		sqlConstructVO.setSql(sql);
		
		return sqlConstructVO;
		
	}
	
	// 获取查询条件
	public static SqlQueryDataVO getQueryDataVo(SqlQueryDataVO sqlQueryDataVO, List<SqlConditionVO> conditions,
	        String expand) {
		
		if (sqlQueryDataVO == null) {
			sqlQueryDataVO = new SqlQueryDataVO();
		}
		
		// 真正用于查询的条件
		List<SqlSearchVO> queryData = sqlQueryDataVO.getQueryData();
		if (queryData == null) {
			queryData = new ArrayList<>();
			sqlQueryDataVO.setQueryData(queryData);
		}
		
		// 遍历queryDataJa , 获取key值
		JSONObject queryDataJo = new JSONObject();
		JSONObject groupByJo = new JSONObject();
		
		for (SqlSearchVO query : queryData) {
			if (Util.equals(query.getIsGroup(), BooleanConstants.YES)) {
				groupByJo.put(query.getName(), query.getVal());
				continue;
			}
			queryDataJo.put(query.getName(), query.getVal());
		}
		
		// 替换freemarker变量
		JSONObject freemarkerParams = new JSONObject();
		if (!CollectionUtils.isEmpty(conditions)) {
			for (SqlConditionVO conditionObj : conditions) {
				if (Util.equals(conditionObj.getParamType(), SearchParamsType.FREEMARKER_PARAMS)) {
					String key = conditionObj.getName() + conditionObj.getCntype(); // 加上查询条件,是为了处理多个查询条件名字相同的情况
					freemarkerParams.put(key, conditionObj.getDesc());
					freemarkerParams.put(conditionObj.getName(), conditionObj.getDesc());// 把不加条件的也存储进来
				}
			}
		}
		
		// 去掉遍历的数据
		Iterator<?> iterator = queryData.iterator();
		while (iterator.hasNext()) {
			SqlSearchVO query = (SqlSearchVO) iterator.next();
			
			// 如果是分组变量,也不需要append到sql后面
			if (Util.equals(query.getIsGroup(), BooleanConstants.YES)) {
				iterator.remove();
			}
			
			// 如果是freemarker变量,则不需要append到sql后面
			String key = query.getName() + query.getCon();
			if (freemarkerParams.containsKey(key)) {
				freemarkerParams.put(key, query.getVal()); // 加条件的值
				freemarkerParams.put(query.getName(), query.getVal());// 不加条件的值
				iterator.remove();
			}
		}
		
		sqlQueryDataVO.setFreemarkerParams(freemarkerParams);
		
		// 字段分组 - 暂时没用到
		String groupByConditionStr = JSONObjectUtil.getValByKey("groupby", expand);
		JSONObject groupByConditionNameJo = new JSONObject();
		if (StringUtil.isNotEmpty(groupByConditionStr)) {
			JSONArray groupByConditionJa = JSONArray.parseArray(groupByConditionStr);
			for (Object object : groupByConditionJa) {
				JSONObject groupByConditionJo = (JSONObject) object;
				groupByConditionNameJo.put(groupByConditionJo.getString("name"), groupByConditionJo.getString("type"));
			}
		}
		
		Map<String, String> paramsData = sqlQueryDataVO.getParamsData();
		if (paramsData == null) {
			paramsData = new HashMap<>();
		}
		
		if (!CollectionUtils.isEmpty(conditions)) {
			for (SqlConditionVO condition : conditions) {
				// 如果是默认的参数,则设置值
				String name = condition.getName();
				String desc = condition.getDesc();
				String cntype = condition.getCntype();
				String json = condition.getJson();// 获取字段格式化参数
				boolean isInit = ((condition.getInit() == 1)
				        || Util.contains(Util.array("is null", "not null"), cntype));
				SqlSearchVO searchVO = new SqlSearchVO();
				searchVO.setName(name);
				searchVO.setCon(cntype);
				
				String val = condition.getVal();
				
				// 从条件本身的数据中获取值
				if (StringUtil.isNotEmpty(val)) {
					val = StringUtil.resetJsonStr(resetQuoteStr(val));
				}
				
				// 从URL中传过来的数据中获取值
				if (paramsData.containsKey(name)) {
					val = paramsData.get(name);
				}
				
				// 从前端传过来的数据中获取值
				if (queryDataJo.containsKey(name) && !isInit) {
					val = queryDataJo.getString(name);
				}
				
				// 判断是否是后台赋值
				Integer dfDataType = condition.getDfDataType();
				if (dfDataType != null) {
					JSONObject dfValJo = new JSONObject();
					dfValJo.put("type", dfDataType);
					dfValJo.put("sdType", ViewSetDataConstants.BACKGROUND);// 赋值类型 后台
					dfValJo.put("val", StringUtil.isEmpty(val) ? json : val);
					val = ViewUtil.getDefaultVal(dfValJo);
				}
				
				searchVO.setVal(val);
				searchVO.setBracket(condition.getBracket());
				searchVO.setAndOr(condition.getAndOr());
				
				// 如果有值,或者是初始化的情况下
				if (!queryDataJo.containsKey(name)) {
					if (StringUtil.isNotEmpty(val) || isInit) {
						queryData.add(searchVO);
						queryDataJo.put(name, val);
					}
				} else {
					// 如果没有值,并且不是默认,则需要删掉这个条件
					if (StringUtil.isEmpty(val) && !isInit) {
						iterator = queryData.iterator();
						while (iterator.hasNext()) {
							SqlSearchVO query = (SqlSearchVO) iterator.next();
							if (Util.equals(query.getName(), name)) {
								iterator.remove();
								break;
							}
						}
					}
				}
				
				// 判断是否是必须的参数 , 如果是,则抛出异常
				if (!isInit && Util.equals(condition.getIsMust(), BooleanConstants.YES)
				        && !queryDataJo.containsKey(name)) {
					String errorMsg = desc + "参数不能为空";
					logger.error(errorMsg);
					throw new BusinessMessage(errorMsg);
				}
			}
		}
		
		return sqlQueryDataVO;
	}
	
	/**
	 * 获取默认值
	 * 
	 * @param dfVal
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2017年9月2日 下午3:01:39
	 */
	public static String getDefaultVal(JSONObject dfVal) {
		
		String val = "";
		String dfType = dfVal.getString("type");
		String dfV = "";
		if (dfVal.containsKey("val")) {
			dfV = dfVal.getString("val");
		}


		switch (dfType) {
			case ViewDataTypeConstants.DATA_NOMAL:
				dfV = dfV.replaceAll(ParamNameConstants.CUR_USERID,
				        StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId()));

//				dfV = dfV.replaceAll(ParamNameConstants.CUR_PROJECTID,
//						StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentProjectId()));

				dfV = dfV.replaceAll(ParamNameConstants.CUR_USERNAME, ContextUtil.getCurrentUserName());
				dfV = dfV.replaceAll(ParamNameConstants.CUR_DATE, DateUtils.getCurrentDate());
				dfV = dfV.replaceAll(ParamNameConstants.CUR_TIME, DateUtils.getCurrentDate("HH:mm:ss"));
				dfV = dfV.replaceAll(ParamNameConstants.CUR_DATE_TIME, DateUtils.getCurrentTime());
				val = dfV;
			break;
			case ViewDataTypeConstants.DATA_CUR_DATE:
				val = DateUtils.formatDate(new Date(),
				        (StringUtil.isNotEmpty(dfV) ? dfV : DateUtils.DEFAULT_DATE_TIME_PATTERN));
			break;
			case ViewDataTypeConstants.DATA_CUR_USER_ID:
				val = StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId());
			break;
			case ViewDataTypeConstants.DATA_CUR_USER_NAME:
				val = ContextUtil.getCurrentUserName();
			break;
			case ViewDataTypeConstants.DATA_SYS_LSH:
				val = ContextUtil.getCurrentUserName();
			break;
			case ViewDataTypeConstants.DATA_RANDOM_NUM:
				int minInt = 0;
				int maxInt = 1000;
				if (!StringUtil.isEmpty(dfV)) {
					if (dfV.indexOf(",") > 0) {
						String[] split = dfV.split(",");
						minInt = Integer.parseInt(split[0]);
						maxInt = Integer.parseInt(split[1]);
					} else {
						maxInt = Integer.parseInt(dfV);
					}
				}
				val = RandomUtils.nextInt(minInt, maxInt) + "";
			break;
			case ViewDataTypeConstants.DATA_CUR_USER_DEPT_ID:
				val = ContextUtil.getCurrentGroupId();
			break;
			case ViewDataTypeConstants.DATA_CUR_USER_DEPT_NAME:
				IGroup currentGroup = ContextUtil.getCurrentGroup();
				if (currentGroup != null) {
					val = currentGroup.getGroupName();
				}else {
					val = "";
				}
			break;
			case ViewDataTypeConstants.DATA_CUR_MAIN_TABLE_ID:
				val = ParamNameConstants.MAIN_TABLE_ID_NOMARL;
			break;
			case ViewDataTypeConstants.ENV:
				val = PropertyUtil.getProperty("spring.profiles.active");
			break;
			default:
			break;
		}
		return val;
	}
	
	/**
	 * 替换单双引号,,配合前端传输文本..
	 * 
	 * @param str
	 * @return
	 * @return String
	 * @author xianggang Date : 2016年4月9日 下午5:14:54
	 */
	public static String resetQuoteStr(String str) {
		if (StringUtils.isEmpty(str)) {
			return "";
		}
		str = str.replaceAll(QutoConstants.SINGLE_QUOTE, "'").replaceAll(QutoConstants.QUOTE, "\"")
		        .replaceAll(QutoConstants.BACKSLASH, "\\\\");
		return str;
	}
	
	// 替换系统变量
	public static String replaceParams(String sql) {
		sql = sql.replaceAll(ParamNameConstants.CUR_USERID,
		        StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId()));
		sql = sql.replaceAll(ParamNameConstants.CUR_ORG_ID,
		        StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentGroupId()));
//		sql = sql.replaceAll(ParamNameConstants.CUR_PROJECTID,
//				StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentProjectId()));
		sql = sql.replaceAll(ParamNameConstants.CUR_USERNAME, ContextUtil.getCurrentUserName());
		sql = sql.replaceAll(ParamNameConstants.CUR_DATE, DateUtils.getCurrentDate());
		sql = sql.replaceAll(ParamNameConstants.CUR_TIME, DateUtils.getCurrentDate("HH:mm:ss"));
		sql = sql.replaceAll(ParamNameConstants.CUR_DATE_TIME, DateUtils.getCurrentTime());
		sql = sql.replaceAll(ParamNameConstants.ENV, PropertyUtil.getProperty("spring.profiles.active"));
		return sql;
	}
	
	/**
	 * 获取where条件 // TODO 这里需要继续优化 需要加上左括号,右括号的功能
	 * 
	 * @param
	 * @param sqlFieldTableAndNameMap
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2016年3月16日 下午6:59:04
	 */
	public static SqlConstructVO getWhere(SqlQueryDataVO sqlQueryDataVO, JSONObject sqlFieldTableAndNameMap) {
		
		SqlConstructVO SqlConstructVO = new SqlConstructVO();
		SqlConstructVO.setWhere("");
		SqlConstructVO.setViewWhere("");
		
		SqlConstructVO.setBackupLogId(sqlQueryDataVO.getBackupLogId());
		
		List<SqlSearchVO> queryData = sqlQueryDataVO.getQueryData();
		if (CollectionUtils.isEmpty(queryData)) {
			return SqlConstructVO;
		}
		
		StringBuilder whereSb = new StringBuilder();
		StringBuilder viewWhereSb = new StringBuilder();
		
		Map<String, Object> paramsMap = new HashMap<>();
		for (SqlSearchVO vo : queryData) {
			
			// 获取查询条件,这里需要通过视图字段转成原始的数据库字段 column
			String name = vo.getName();
			if (!sqlFieldTableAndNameMap.containsKey(name)) {
				name = StringUtil.underlineToCamel(name.toLowerCase());
			}
			String searchName = sqlFieldTableAndNameMap.getString(name);
			if (StringUtil.isEmpty(searchName)) {
				continue;
			}
			vo.setName(searchName);
			whereSb.append(ViewUtil.getWhereByCon(vo));
			
			vo.setName(name);
			viewWhereSb.append(ViewUtil.getWhereByCon(vo));
			
			String val = vo.getVal();
			paramsMap.put(name, val);
			
			if (DateUtils.isDate(val)) {
				if (vo.getCon().indexOf(">") >= 0) {
					paramsMap.put(name + "Start", val);
				}
				if (vo.getCon().indexOf("<") >= 0) {
					paramsMap.put(name + "End", val);
				}
			}
			
		}
		SqlConstructVO.setParamsMap(paramsMap);
		
		if (whereSb.length() > 0) {
			SqlConstructVO.setWhere(" " + whereSb.toString());
			SqlConstructVO.setViewWhere(" " + viewWhereSb.toString());
		}
		
		return SqlConstructVO;
	}
	
	// 根据查询条件, 构造where条件
	public static String getWhereByCon(SqlSearchVO searchVO) {
		String con = searchVO.getCon();
		String val = searchVO.getVal();
		String name = searchVO.getName();
		if (name.indexOf(".") > 0) {
			String[] tempName = name.split("\\.");
			name = " " + tempName[0] + ".`" + tempName[1] + "` ";
		} else {
			name = " `" + name + "` ";
		}
		
		String sql = "";
		
		if (StringUtils.isEmpty(val) || val.equals("null")) {
			val = "";
		}
		
		// 括号
		String bracket = searchVO.getBracket();
		String andOr = searchVO.getAndOr();
		
		// 如果是左括号,则需要在开头加上 (
		if ("(".equals(bracket)) {
			andOr = andOr + " " + bracket + " ";
		}
		
		switch (con) {
			case "=":
			case ">=":
			case ">":
			case "<":
			case "<=":
			case "<>":
				if (StringUtils.isNumeric(val)) {
					sql = andOr + name + con + " " + val + " ";
				} else {
					sql = andOr + name + con + " '" + val + "' ";
				}
			break;
			case "llike":
			case "rlike":
			case "like":
				sql = andOr + " ";
				String[] vals = val.split(",");
				String llike = "%";
				String rlike = "%";
				if (con.equals("llike")) {
					rlike = "";
				}
				if (con.equals("rlike")) {
					llike = "";
				}
				for (int i = 0; i < vals.length; i++) {
					sql += name + " like '" + llike + vals[i] + rlike + "' ";
					if (i != vals.length - 1) {
						sql += " or ";
					}
				}
			break;
		
			case "in":
			case "not in":
				
				if (StringUtils.isNotEmpty(val)) {
					vals = val.split(",");
					val = "";
					for (String v : vals) {
						if (StringUtils.isNumeric(v)) {
							val += v + ",";
						} else {
							val += "'" + v + "',";
						}
					}
					val = val.substring(0, val.length() - 1);
				}
				sql = andOr + " " + name + " " + con + " (" + val + ") ";
				
			break;
			case "is null":
				if (Util.equals(val, BooleanConstants.YES)) {
					sql = andOr + " (" + name + " is null or " + name + " = '')";
				} else {
					sql = andOr + " (" + name + " is not null and " + name + " != '')";
				}
			break;
			case "not null":
				if (Util.equals(val, BooleanConstants.NO)) {
					sql = andOr + " (" + name + " is null or " + name + " = '')";
				} else {
					sql = andOr + " (" + name + " is not null and " + name + " != '')";
				}
			break;
			default:
			
			break;
		}
		
		// 如果是右括号,则需要在最后加上 )
		if (")".equals(bracket)) {
			sql += bracket;
		}
		
		return sql;
	}
	
	/**
	 * 获取字段列表和查询字段
	 * 
	 * @author xianggang
	 * @date: 2016年11月11日 上午10:25:44
	 * @param expandJo
	 */
	public static SqlViewVO getFieldColumnJo(List<FormCustSqlFields> commonFieldList, JSONObject expandJo) {
		
		String realSqlColumn = "";
		
		Map<String, Object> sqlJo = new HashMap<>();
		
		String sumSqlColumn = "";
		for (FormCustSqlFields field : commonFieldList) {
			String realSearchSqlColumn = ViewUtil.getRealSearchSqlColumn(field);
			realSqlColumn += realSearchSqlColumn + ",";
		}
		// 构造真实需要查询的字段 sql
		realSqlColumn = realSqlColumn.substring(0, realSqlColumn.length() - 1);
		
		if (StringUtils.isNotEmpty(sumSqlColumn)) {
			sumSqlColumn = sumSqlColumn.substring(0, sumSqlColumn.length() - 1);
		}
		
		sqlJo.put("realSqlColumn", realSqlColumn);
		sqlJo.put("sumSqlColumn", sumSqlColumn);
		
		SqlViewVO sqlViewVO = new SqlViewVO();
		sqlViewVO.setRealSqlColumn(realSqlColumn);
		sqlViewVO.setSumSqlColumn(sumSqlColumn);
		
		return sqlViewVO;
	}
	
	// 获取真实的查询字段
	public static String getRealSearchSqlColumn(FormCustSqlFields field) {
		
		String realSqlColumn = "";
		if (BeanUtils.isEmpty(field)) {
			return "";
		}
		String fieldName = field.getFieldName();
		// 需要注意的是 空格不能丢掉
		if (field.getDataType().equals(SqlConstants.COLUMNTYPE_DATE)) {
			realSqlColumn = " date_format(" + fieldName + ",'%Y-%c-%d %H:%i:%s') as `" + fieldName + "`";
		} else {
			realSqlColumn = (" `" + fieldName + "`");
		}
		return realSqlColumn;
	}
	
	// 处理数据中的空值以及特殊信息
	public static String changeQueryListToJSONStr(List<Map<String, Object>> queryForList) {
		
		JSONArray list = JSONArray.parseArray(JSONArray.toJSONString(queryForList));
		
		String listStr = list.toString();
		
		// 替换字段为空的内容,否则回报控制针异常
		listStr = listStr.replaceAll(":null", ":\"\"");
		// 替换掉 双反斜杠转换时 丢失的bug
		listStr = listStr.replaceAll("\\\\\\\\", "/");
		
		return listStr;
	}
	
	// 处理查询数据之后的一些特殊数据. 这里以后可以重构一下,用工厂模式
	public static Object handleData(String code, String list, JdbcTemplate newJdbcTemplate, SqlViewVO sqlViewVO) {
		
		// 设置扩展的数据
		List<FormCustSqlFields> commonFieldList = sqlViewVO.getFieldList();
		
		logger.info("处理查询数据之后的一些特殊数据,code:{}", code);
		
		try {
			JSONArray listData = JSONArray.parseArray(list);
			if (!listData.isEmpty()) {
				
				// 脱敏
				TuoMingUtil.tuoMing(commonFieldList, listData);
				
				// 如果有一些特殊的控件,需要单独重新获取数据,
			}
			return listData;
		} catch (Exception e) {
			logger.error("处理查询数据之后的一些特殊数据-数据转json异常,code:{},e:{}", code, e);
			return list;
		}
	}
	
	/**
	 * 
	* 获取可操作的字段
	* @param settings
	* @param type
	* 
	* @return List<FormCustSqlFields>
	* 
	* @author xianggang
	* @date: 2016年3月1日 上午11:38:25
	*/
	public static List<FormCustSqlFields> getFields(List<FormCustSqlFields> settings, String type) {
		
		List<FormCustSqlFields> list = new ArrayList<FormCustSqlFields>();
		for (FormCustSqlFields vo : settings) {
			switch (type) {
				case "1":
					// 可添加
					if (FieldsOperTypeCode.OPERABLE.contains(vo.getAddAble())) {
						list.add(vo);
					}
				break;
				case "2": // 可编辑
				case "3": // 可删除
					if (FieldsOperTypeCode.OPERABLE.contains(vo.getEditAble())) {
						list.add(vo);
					}
				break;
				case "5": // 可导出
					if (vo.getExportAble() == 1) {
						list.add(vo);
					}
				break;
				default:
				break;
			}
		}
		return list;
	}
	
	// 获取URL的参数
	@SuppressWarnings("unchecked")
	public static Map<String, String> getUrlParams(String queryData) {
		ObjectMapper mapper = new ObjectMapper();
		Map<String, String> urlParams = new HashMap<>();
		try {
			urlParams = mapper.readValue(queryData, Map.class);
		} catch (Exception e) {
		}
		if (urlParams != null) {
			// 移除一些关键字参数
			urlParams.remove("queryData");
			urlParams.remove("groupBy");
			urlParams.remove("paramsData");
			urlParams.remove("freemarkerParams");
		}
		return urlParams;
	}
}
